package com.baihuabooks.scoresys.servlets;

import com.alibaba.fastjson.JSON;
import com.baihuabooks.scoresys.common.DBUtil;
import com.baihuabooks.scoresys.common.Page;
import com.baihuabooks.scoresys.entity.Customer;
import com.baihuabooks.scoresys.entity.Response;
import org.apache.commons.lang3.StringUtils;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Thinkpad on 2016/5/15 0015.
 */
public class CustomerServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        String search = req.getParameter("search");
        String limit = req.getParameter("limit");
        String offset = req.getParameter("offset");

        int limitInt = Integer.valueOf(limit);
        int offsetInt = Integer.valueOf(offset);

        int total = selectTotal();
        List<Customer> customers = selectPage(offsetInt, limitInt, search);

        Page<Customer> page = new Page<Customer>(total, customers);
        resp.getWriter().print(JSON.toJSONString(page));
        resp.getWriter().flush();

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        String customerId = req.getParameter("customerId");
        String cardNo = req.getParameter("card_no");
        String name = req.getParameter("name");
        String mobile = req.getParameter("mobile");

        Response response = new Response();

        if(StringUtils.isEmpty(cardNo)) {
            response.setCode(1);
            response.setDesc("卡号为空");
        } else if(StringUtils.isEmpty(name)) {
            response.setCode(1);
            response.setDesc("姓名为空");
        } else if(StringUtils.isNotBlank(customerId)){
            // Modify
            Connection conn = null;
            PreparedStatement stmt = null;

            String sql = "UPDATE CUSTOMER SET CARD_NO=?, NAME=?, MOBILE=?, UPDATE_TIME=NOW() WHERE ID=?";
            try {
                conn = DBUtil.getInstance().getConnection();
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, cardNo);
                stmt.setString(2, name);
                stmt.setString(3, mobile);
                stmt.setInt(4, Integer.valueOf(customerId));

                stmt.executeUpdate();
                response.setCode(0);
            } catch (SQLException e) {
                e.printStackTrace();
                response.setCode(1);
                response.setDesc("数据入库异常，请联系管理员");
            } finally {
                DBUtil.close(stmt, conn);
            }
        } else {
            // Add
            Connection conn = null;
            PreparedStatement stmt = null;

            String sql = "INSERT INTO CUSTOMER(CARD_NO, NAME, MOBILE, create_time) VALUES(?,?,?,NOW())";
            try {
                conn = DBUtil.getInstance().getConnection();
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, cardNo);
                stmt.setString(2, name);
                stmt.setString(3, mobile);

                stmt.executeUpdate();
                response.setCode(0);
            } catch (SQLException e) {
                e.printStackTrace();
                response.setCode(1);
                response.setDesc("数据入库异常，请联系管理员");
            } finally {
                DBUtil.close(stmt, conn);
            }
        }

        resp.getWriter().print(response.toString());
    }

    private int selectTotal() {
        String sql = "SELECT count(1) as cnt FROM customer";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        int total = 0;

        try {
            conn = DBUtil.getInstance().getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if(rs.next()) {
                total = rs.getInt("cnt");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, stmt, conn);
        }

        return total;
    }

    private List<Customer> selectPage(int offset, int limit, String search) {

        String sql =
                   " SELECT                      "
                 + " 	C.ID,                    "
                 + " 	C.CARD_NO,               "
                 + " 	C.`NAME`,                "
                 + " 	C.MOBILE,                "
                 + " 	DATE_FORMAT(             "
                 + " 		C.CREATE_TIME,       "
                 + " 		'%Y-%m-%d %H:%i:%s'  "
                 + " 	),                       "
                 + " 	C.SCORE                  "
                 + " FROM                        "
                 + " 	CUSTOMER C               ";

        if(StringUtils.isNotBlank(search)) {
            sql +=     " WHERE                                   "
                     + "	(                                    "
                     + "		C.CARD_NO LIKE CONCAT(?, '%')    "
                     + "		OR C.MOBILE LIKE CONCAT(?, '%')  "
                     + "	)                                    ";

        }

        sql +=    "ORDER BY                            "
                + "	C.CREATE_TIME DESC LIMIT ?,?         ";

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        List<Customer> customers = new ArrayList<Customer>();

        try {
            conn = DBUtil.getInstance().getConnection();
            stmt = conn.prepareStatement(sql);

            if(StringUtils.isNotBlank(search)) {
                stmt.setString(1, search);
                stmt.setString(2, search);
                stmt.setInt(3, offset);
                stmt.setInt(4, limit);
            } else {
                stmt.setInt(1, offset);
                stmt.setInt(2, limit);
            }

            rs = stmt.executeQuery();

            while (rs.next()) {
                Customer customer = new Customer();
                customer.setId(rs.getInt(1));
                customer.setCardNo(rs.getString(2));
                customer.setName(rs.getString(3));
                customer.setMobile(rs.getString(4));
                customer.setCreateTime(rs.getString(5));
                customer.setScore(rs.getInt(6));
                customers.add(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, stmt, conn);
        }

        return customers;
    }
}
